﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Specialized;

public class DataBaseMethods
{

    #region perform queries
    private static string GetConnectionString()
    {
        const string _connectionStringsName = "AgentBillingConnectionString";

        System.Configuration.ConnectionStringSettingsCollection config = System.Configuration.ConfigurationManager.ConnectionStrings;
        for (int i = 0; i < config.Count; i++)
        {
            if (config[i].Name.Equals(_connectionStringsName, StringComparison.OrdinalIgnoreCase))
                return config[i].ToString();
        }
        return String.Empty;
    }

    public static DataTable ExecuteSelectQuery(string query)
    {
        //open connection
        using (SqlConnection conn = new SqlConnection(GetConnectionString()))
        {
            conn.Open();
            //create command
            SqlCommand command = new SqlCommand(query, conn);
            using (SqlDataAdapter da = new SqlDataAdapter() { SelectCommand = command })
            {
                //retireve data
                DataTable dt = new DataTable();
                //retrieve data
                da.Fill(dt);
                //return the result
                conn.Close();
                return dt;
            }
        }
    }

    public static int ExecuteNonQuery(string query)
    {
        //open connection
        using (SqlConnection conn = new SqlConnection(GetConnectionString()))
        {
            conn.Open();
            // command
            SqlCommand command = new SqlCommand(query, conn);
            //retrieve data
            int result = command.ExecuteNonQuery();
            //return the result
            conn.Close();
            return result;
        }
    }

    public static string ExecuteScalar(string query)
    {
        //open connection
        using (SqlConnection conn = new SqlConnection(GetConnectionString()))
        {
            conn.Open();
            //create command
            SqlCommand command = new SqlCommand(query, conn);
            string returnValue;
            try
            {
                returnValue = command.ExecuteScalar().ToString();
            }
            catch (NullReferenceException)
            {
                return "";
            }
            //return the result
            conn.Close();
            return returnValue;
        }
    }

    public static int ExecuteIdentityInsert(string query, string idColumn)
    {
        //open connection
        using (SqlConnection conn = new SqlConnection(GetConnectionString()))
        {
            conn.Open();
            //create id parameter
            idColumn = "@" + idColumn;
            SqlParameter idparam = new SqlParameter(idColumn, SqlDbType.Int) { Direction = ParameterDirection.Output };
            //add identity part
            query += String.Format(" SET {0} = SCOPE_IDENTITY()", idColumn);
            //create command
            SqlCommand command = new SqlCommand(query, conn);
            command.Parameters.Add(idparam);
            command.ExecuteScalar();
            return ((int)idparam.Value);
        }
    }

    public static void uploadDT2DB(DataTable dt, string TableName, bool NeedsIdentity = false)
    {
        // Add identity column to Datatable
        if (NeedsIdentity)
        {
            DataColumn dc = dt.Columns.Add("RowNo", System.Type.GetType("System.Int32"));
            dc.SetOrdinal(0);
        }

        //upload datatable to db
        using (SqlConnection conn = new SqlConnection(GetConnectionString()))
        {
            conn.Open();
            //open bulnk copy
            SqlBulkCopy sbc = new SqlBulkCopy(conn) { /*perform update*/DestinationTableName = TableName };
            sbc.WriteToServer(dt);
            //close
            conn.Close();
        }
        return;
    }
    #endregion

    #region generate queries
    public static String generateInsertQuery(String tableName, NameValueCollection values)
    {
        String query = String.Empty;
        List<String> systemEntries = new List<String> { "id", "oper" };

        //get list of columns
        query = "INSERT INTO " + tableName + "(";
        for (int i = 0; i < values.Count; i++)
        {
            if (systemEntries.Contains(values.GetKey(i))) continue;
            query += " " + values.GetKey(i) + " ,";
        }

        //get all values
        query = query.Substring(0, query.Length - 1) + " ) VALUES ( ";
        for (int i = 0; i < values.Count; i++)
        {
            if (systemEntries.Contains(values.GetKey(i))) continue;
            query += " '" + values.Get(i) + "' ,";
        }

        //remove last comma
        query = query.Substring(0, query.Length - 1) + " ) ";

        return query;
    }

    public static String generateUpdateQuery(String tableName, NameValueCollection values, List<String> primaryKey)
    {
        String query = String.Empty;
        List<String> systemEntries = new List<String> { "id", "oper" };

        query = "UPDATE " + tableName + " SET ";

        //get statment in format: column_name = 'column_value',...
        for (int i = 0; i < values.Count; i++)
        {
            if (systemEntries.Contains(values.GetKey(i)) || primaryKey.Contains(values.GetKey(i))) { continue; }
            query += values.GetKey(i) + " = '" + values.Get(i) + "' ,";
        }

        //add query part, WHERE
        query = query.Substring(0, query.Length - 1) + " WHERE ";
        foreach (String str in primaryKey)
            query += str + " = '" + values.Get(str) + "' ,";

        //remove last extra comma
        query = query.Substring(0, query.Length - 1);

        return query;
    }

    public static String generateDeleteQuery(String tableName, String key, String value)
    {
        return ("DELETE FROM " + tableName + " WHERE " + key + " = '" + value + "' ");
    }

    #endregion
}
